package com.example.castday1.test;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.example.castday1.bean.UserA;
import com.example.castday1.mapper.UserMapper;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author : 徐闯 mpcast
 * @Date : 2021-05-22 22:05:31
 * @Description : 条件构造器案例
 **/
@RunWith(SpringRunner.class)
@SpringBootTest
public class C_TestQueryByWrapper {
    @Autowired
    private UserMapper userMapper;
    // 案例1 : 查询年龄大于20的学生信息
    //  age>20
    @Test
    public void queryInfoCastOne(){
        QueryWrapper<UserA> queryWrapper = new QueryWrapper();
        //QueryWrapper<UserA> queryWrapper1 = Wrappers.query();
        //queryWrapper.last("where age >20");
        queryWrapper.gt("age",20);
        List<UserA> userAList = userMapper.selectList(queryWrapper);
        userAList.forEach(System.out::println);
    }
    // 案例2 : 查询年龄在20到25之间 并且 姓名包含z的用户信息
    //age between 20 and 50 and name like '%z%'
    @Test
    public void queryInfoCastTwo(){
        QueryWrapper<UserA> queryWrapper = Wrappers.query();
        queryWrapper.between("age",20,25);
        queryWrapper.like("name","z");
        List<UserA> userAList =userMapper.selectList(queryWrapper);
        userAList.forEach(System.out::println);
    }
    //案例3: 查询年龄在是 21 23 25 27 30 中或者邮箱不为空的用户信息
    // age in(21,23,25,27,30) or email is not null
    @Test
    public void queryInfoCastThree(){
        List<UserA> userAList =userMapper.selectList(
                new QueryWrapper<UserA>()
                    .in("age",21,22,23,24,25)
                    .or()
                    .isNotNull("email")
        );
        userAList.forEach(System.out::println);
    }
    // 案例4 : 查询年龄小于等于20的所有用户信息,并且根据降序排列
    // age<=20 order by id desc
    @Test
    public void queryInfoCastFour(){
        List<UserA> userAList = userMapper.selectList(
                new QueryWrapper<UserA>()
                        .le("age",20)
                        .orderByDesc("id")
        );
        userAList.forEach(System.out::println);
    }
    // 案例5: 查询年龄等于19或者名字为zs的员工信息
    // age=19 or name = zs
    @Test
    public void queryInfoCastFive(){
        Map<String,Object> map = new HashMap();
        map.put("age",19);
        map.put("name","zs");
        List<UserA> userAList = userMapper.selectList(
                new QueryWrapper<UserA>()
                    /*.eq("age",19)
                    .or()
                    .eq("name","张三")*/
                    .allEq(map)
        );
        userAList.forEach(System.out::println);
    }
    // 案例6:查询年龄最大的用户信息
    //  age = (select max(age) from user) //找不到满足需求的方法
    //  age in (select max(age) from user)
    @Test
    public void queryInfoCastSix(){
        List<UserA> userAList = userMapper.selectList(
                new QueryWrapper<UserA>()
                        .inSql("age","select max(age) from user")
        );
        userAList.forEach(System.out::println);
    }//案例7:查询年龄最大的三个用户的信息
    // order by age desc limit 3
    @Test
    public void queryInfoCastSeven(){
        List<UserA> userAList = userMapper.selectList(
            new QueryWrapper<UserA>()
                .orderByDesc("age")
                    //last 将sql语句拼接到最后
                    //安全性低 存在sql注入的风险
                .select("name as username","age")
                .last(" limit 0,3")
                //.select("name","age")
        );
        userAList.forEach(System.out::println);
    }
}
